﻿using System;
using System.Windows.Forms;
using WeifenLuo.WinFormsUI.Docking;
using HRM.HRMServiceProxy;
using System.Data;
using System.IO;
using HRM.Utilities;
using System.Collections.Generic;

namespace HRM
{
    public partial class frmInsuranceReport : FormBase
    {
        string _month;
        string _year;
        string _ReportType;
        string _ReportText;
        int _IsAll;
        public int IsAll
        {
            get { return _IsAll; }
            set { _IsAll = value; }
        }
        public string ReportText
        {
            get { return _ReportText; }
            set { _ReportText = value; }
        }
        public string ReportType
        {
            get { return _ReportType; }
            set { _ReportType = value; }
        }
        public string Month
        {
            get { return _month; }
            set { _month = value; }
        }
        public string Year
        {
            get { return _year; }
            set { _year = value; }
        }
        string _period;
        public string Period
        {
            get { return _period; }
            set { _period = value; }
        }
        DialogResult dlgResult;
        public frmInsuranceReport()
        {
            InitializeComponent();
            //LoadLanguage();
            this.KeyPreview = true;
            dlgResult = DialogResult.Cancel;
            txtPeriod.Text = "1";
            txtMonth.Text = DateTime.Now.Date.Month.ToString();
            txtYear.Text = DateTime.Now.Date.Year.ToString();
        }

        public override void Show_Form()
        {
            this.ShowDialog();
        }

        void LoadLanguage()
        {
            if (this.Tag != null)
                this.Text = UpdateUI.GetString(this.Tag.ToString());
            UpdateUI.Load_Panel(ref panel2);

        }
        private void btnView_Click(object sender, EventArgs e)
        {
            _period = txtPeriod.Text;
            _month = txtMonth.Text;
            _year = txtYear.Text;
            if (radOmDau.Checked)
            {
                _ReportType = "Trợ cấp ốm đau";
                _ReportText = "Trợ cấp ốm đau";
            }
            else if (radThaiSan.Checked)
            {
                _ReportType = "Trợ cấp thai sản";
                _ReportText = "Trợ cấp thai sản";
            }
            else if (rdnBHXH_Mau02aTBH.Checked)
            {
                _ReportType = "BHXH_Mau02aTBH";
                _ReportText = "Mau02";
                _IsAll = 0;
            }
            else if (rad02All.Checked)
            {
                _ReportType = "BHXH_Mau02aTBH_All";
                _ReportText = "Mau02_TCT";
                _IsAll = 1;
            }
            else if (rdnBHXHMau03.Checked)
            {
                _ReportType = "BHXHMau03";
                _ReportText = "Mau03";
            }
            else if (rdnBHXHMau08.Checked)
            {
                _ReportType = "BHXHMau08";
                _ReportText = "Mau08";
            }
            else if (radBHXH_01a.Checked)
            {
                _ReportType = "BHXHMau01a";
                _ReportText = "Mau01a";

            }
            else if (radDieuChinh.Checked)
            {
                _ReportType = "DieuChinhHS_BHXH_BHYT";
                _ReportText = "DieuChinhNoiKhamChuaBenh";

            }
            //SalaryClient wcfSalary = Service.Salary();
            //if (DateTime.Now.Date.Month == Convert.ToInt32(_month) && DateTime.Now.Year == Convert.ToInt32(_year))
            //    wcfSalary.Salary_Calculate(Convert.ToInt32(_month), Convert.ToInt32(_year));
            dlgResult = DialogResult.OK;
            this.Close();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            dlgResult = DialogResult.Cancel;
            this.Close();
        }

        private void frmInsuranceReport_KeyUp(object sender, KeyEventArgs e)
        {

        }

        private void frmInsuranceReport_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult = dlgResult;
        }

        private void radBHXH_01a_CheckedChanged(object sender, EventArgs e)
        {
            txtYear.Enabled = true;
            txtMonth.Enabled = true;
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            string fileName = "";
            string branchname = "";
            string _title = "";
            List<string> lstSheetName = new List<string>();
            BranchClient _wcfbranch = Service.Branch();
            DetailClient _wcfDetail = Service.Detail();
            BranchInfo inf = null;
            DataTable dt = new DataTable();
            DataSet ds = null;
            DataTable dtEx = new DataTable();
            bool _IsMau02 = false;
            int MonthVal = 0;
            int PeriodVal = 0;
            int YearVal = 0;
            MonthVal = UISetting.GetIntNo(txtMonth.Text.Trim());
            PeriodVal = UISetting.GetIntNo(txtPeriod.Text.Trim());
            YearVal = UISetting.GetIntNo(txtYear.Text.Trim());

            inf = _wcfbranch.Branch_GetInfo(Program.intBranchID);
            if (inf != null)
                branchname = inf.Name;
            if (rdnBHXH_Mau02aTBH.Checked)
            {
                _IsMau02 = true;
                _title = "MẪU 02a TBH";
                fileName = "Mau02a.xls";
                dt = _wcfDetail.Detail_Mau02_Excel(PeriodVal, MonthVal, YearVal, Program.strBranchBHXH, false).Tables[0];

                //Them cot TjnhLaj, danh dau x het, hjepdh: 08/09/2012
                TinhLaiBaoHiem("Mau02a", MonthVal, YearVal, ref dt);
            }
            else if (rad02All.Checked)
            {
                _IsMau02 = true;
                _title = "MẪU 02a Toàn công ty";
                fileName = "Mau02a_TCT.xls";
                //dt = _wcfDetail.Detail_Mau02_Excel(PeriodVal, MonthVal, YearVal, Program.strBranchBHXH, true).Tables[0];
                ds = _wcfDetail.Detail_Mau02_Excel(PeriodVal, MonthVal, YearVal, Program.strBranchBHXH, true);
                switch (ds.Tables.Count)
                {
                    case 1:
                        ds.Tables[0].TableName = "DS BH Xã hội";
                        break;
                    case 2:
                        ds.Tables[0].TableName = "DS BH Xã hội";
                        ds.Tables[1].TableName = "DS BH Thất nghiệp";
                        break;
                    default:
                        break;
                }
            }
            else if (rdnBHXHMau03.Checked)
            {
                _title = "MẪU 03a TBH";
                fileName = "Mau03a.xls";
                dt = _wcfDetail.Detail_Mau03_Excel(PeriodVal, MonthVal, YearVal, Program.strBranchBHXH).Tables[0];

                //06/05 a Tuy, ko lấy phương án chức danh tại mẫu này



                //duyệt qua các row, so đến tháng với tháng xuất báo cáo và xem PA tăng để đánh dấu là có tính lãi hay ko
                //Them cot TjnhLaj, danh dau x het, hjepdh: 08/09/2012

                //foreach (DataRow dr in dt.Rows)
                //    dr["TinhLai"] = "X";
                TinhLaiBaoHiem("Mau03a", MonthVal, YearVal, ref dt);
            }
            try
            {
                if (ds == null)
                {
                    int rowscount = dt.Rows.Count;
                    int colscount = dt.Columns.Count;

                    if (rowscount > 0)
                    {
                        //Xây dựng các cột trước
                        for (int i = 0; i < colscount; i++)
                        {
                            dtEx.Columns.Add(new DataColumn(UnicodeConvert.UnicodeToTCVN3(dt.Columns[i].ColumnName), typeof(string)));
                        }
                        //Gán dữ liệu vào các dòng tương ứng theo cột
                        DataRow dr;
                        for (int j = 0; j < rowscount; j++)
                        {
                            dr = dtEx.NewRow();
                            //Liệt kê các cột theo thứ tự côt của bảng gốc, kết hợp gán dữ liệu dòng tương ứng cột
                            for (int k = 0; k < colscount; k++)
                            {
                                if (dt.Columns[k].DataType == typeof(String))
                                    dr[k] = UnicodeConvert.UnicodeToTCVN3(dt.Rows[j][k].ToString());
                                else
                                    dr[k] = dt.Rows[j][k];
                            }
                            dtEx.Rows.InsertAt(dr, j);
                        }


                        ExportData2Excel(_title, dtEx, Path.GetTempPath() + "\\" + fileName, _IsMau02);
                        try
                        {
                            System.Diagnostics.Process.Start(Path.GetTempPath() + "\\" + fileName);
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("Không mở được File \n" + Path.GetTempPath() + "\\" + fileName, "Thông báo");
                        }

                    }
                    else
                        MessageBox.Show("Tháng này không có phát sinh", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    DataSet dsTmp = new DataSet();
                    foreach (DataTable dtTmp in ds.Tables)
                    {
                        int rowscount = dtTmp.Rows.Count;
                        int colscount = dtTmp.Columns.Count;

                        dtEx = null;
                        dtEx = dtTmp.Clone();
                        //Xây dựng các cột trước
                        for (int i = 0; i < colscount; i++)
                        {
                            dtEx.Columns[i].ColumnName = UnicodeConvert.UnicodeToTCVN3(dtTmp.Columns[i].ColumnName);
                        }
                        //Gán dữ liệu vào các dòng tương ứng theo cột
                        DataRow dr;
                        for (int j = 0; j < rowscount; j++)
                        {
                            dr = dtEx.NewRow();
                            //Liệt kê các cột theo thứ tự côt của bảng gốc, kết hợp gán dữ liệu dòng tương ứng cột
                            for (int k = 0; k < colscount; k++)
                            {
                                if (dtTmp.Columns[k].DataType == typeof(String))
                                    dr[k] = UnicodeConvert.UnicodeToTCVN3(dtTmp.Rows[j][k].ToString());
                                else
                                    dr[k] = dtTmp.Rows[j][k];
                            }
                            dtEx.Rows.InsertAt(dr, j);
                        }
                        dsTmp.Tables.Add(dtEx);
                    }
                    ds.Clear();
                    ds.Dispose();


                    ExportData2Excel(_title, dsTmp, Path.GetTempPath() + "\\" + fileName, _IsMau02);

                    dsTmp.Clear();
                    dsTmp.Dispose();
                    try
                    {
                        System.Diagnostics.Process.Start(Path.GetTempPath() + "\\" + fileName);
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("Không mở được File \n" + Path.GetTempPath() + "\\" + fileName, "Thông báo");
                    }
                }
            }
            catch (Exception)
            {
                MessageBox.Show("File " + Path.GetTempPath() + "\\" + fileName + " đang được mở bằng một ứng dụng khác.\nHãy chọn file khác!", "Thông báo");
            }
        }

        private void TinhLaiBaoHiem(string Loai, int MonthVal, int YearVal, ref DataTable dt)
        {
            dt.Columns.Add("TinhLai");
            bool TinhLai = false;
            string PATinhLai = "AD/ON/DC/TC/TD/TM/TN";

            foreach (DataRow dr in dt.Rows)
            {
                TinhLai = false;
                //
                switch (Loai)
                {
                    case "Mau03a":
                        string PA = dr["Detail_InsTypeCode"].ToString();
                        string ToDate = dr["Detail_FromDate"].ToString();
                        int DenThang = Convert.ToInt32(ToDate.Split(new char[] { '/' })[0]);
                        int DenNam = Convert.ToInt32(ToDate.Split(new char[] { '/' })[1]);
                        decimal TongMucCu = (decimal)dr["OldAmount"] + (decimal)dr["Detail_OldPositionFactor"] + (decimal)dr["Detail_OldOverFactor"] + (decimal)dr["Detail_OldJobFactor"] + (decimal)dr["Detail_OldAreaFactor"];
                        decimal TongMucMoi = (decimal)dr["NewAmount"] + (decimal)dr["Detail_NewPositionFactor"] + (decimal)dr["Detail_NewOverFactor"] + (decimal)dr["Detail_NewJobFactor"] + (decimal)dr["Detail_NewAreaFactor"];
                        int SoThang = (YearVal - DenNam) * 12 + (MonthVal - DenThang);
                        //
                        //TinhLai = PATinhLai.Contains(PA) && SoThang > 1 && TongMucMoi > TongMucCu;
                        TinhLai = PATinhLai.Contains(PA) && SoThang > 1; //6/5: Truy giảm cũng tính lãi
                        break;
                    case "Mau02a":
                        PA = dr["Detail_InsTypeCode"].ToString();
                        ToDate = dr["Detail_FromDate"].ToString();
                        DenThang = Convert.ToInt32(ToDate.Split(new char[] { '/' })[0]);
                        DenNam = Convert.ToInt32(ToDate.Split(new char[] { '/' })[1]);
                        //TongMucCu = (decimal)dr["OldAmount"] + (decimal)dr["Detail_OldPositionFactor"] + (decimal)dr["Detail_OldOverFactor"] + (decimal)dr["Detail_OldJobFactor"] + (decimal)dr["Detail_OldAreaFactor"];
                        TongMucCu = (Decimal)dr["Tongmuccu"];
                        //TongMucMoi = (decimal)dr["NewAmount"] + (decimal)dr["Detail_NewPositionFactor"] + (decimal)dr["Detail_NewOverFactor"] + (decimal)dr["Detail_NewJobFactor"] + (decimal)dr["Detail_NewAreaFactor"];
                        TongMucMoi = (Decimal)dr["Tongmucmoi"];
                        SoThang = (YearVal - DenNam) * 12 + (MonthVal - DenThang);
                        //
                        //TinhLai = PATinhLai.Contains(PA) && SoThang > 1 && TongMucMoi > TongMucCu;
                        TinhLai = PATinhLai.Contains(PA) && SoThang > 1; //6/5: Truy giảm cũng tính lãi
                        break;
                }
                //
                if (TinhLai)
                    dr["TinhLai"] = "X";
            }
        }

        private void ExportData2Excel(string _Title, DataTable dt, string _FileName, bool _IsMau02)
        {
            if (dt.Columns.Contains("RowNumber"))
                dt.Columns["RowNumber"].Caption = "STT";

            if (dt.Columns.Contains("Detail_InsBookNo"))
                dt.Columns["Detail_InsBookNo"].Caption = "SOBHXH";

            if (dt.Columns.Contains("FullName"))
                dt.Columns["FullName"].Caption = "HOTEN";

            if (dt.Columns.Contains("Detail_FromDate"))
                dt.Columns["Detail_FromDate"].Caption = "TUTHANG";

            if (dt.Columns.Contains("Detail_ToDate"))
                dt.Columns["Detail_ToDate"].Caption = "DENTHANG";

            if (dt.Columns.Contains("InsRate"))
                dt.Columns["InsRate"].Caption = "TYLE";

            if (dt.Columns.Contains("OldAmount"))
                dt.Columns["OldAmount"].Caption = "ML_DK";

            if (dt.Columns.Contains("OldMedicalAmount"))
                dt.Columns["OldMedicalAmount"].Caption = "MLYT_DK";

            if (dt.Columns.Contains("NewAmount"))
                dt.Columns["NewAmount"].Caption = "ML";

            if (dt.Columns.Contains("NewMedicalAmount"))
                dt.Columns["NewMedicalAmount"].Caption = "MLYT";

            if (dt.Columns.Contains("Detail_OldBasicFactor"))
                dt.Columns["Detail_OldBasicFactor"].Caption = "HSL_DK";

            if (dt.Columns.Contains("Detail_OldPositionFactor"))
                dt.Columns["Detail_OldPositionFactor"].Caption = "PC1_DK";

            if (dt.Columns.Contains("Detail_OldOverFactor"))
                dt.Columns["Detail_OldOverFactor"].Caption = "PC2_DK";

            if (dt.Columns.Contains("Detail_OldJobFactor"))
                dt.Columns["Detail_OldJobFactor"].Caption = "PC3_DK";

            if (dt.Columns.Contains("Detail_OldAreaFactor"))
                dt.Columns["Detail_OldAreaFactor"].Caption = "PC4_DK";

            if (dt.Columns.Contains("Detail_NewBasicFactor"))
                dt.Columns["Detail_NewBasicFactor"].Caption = "HSL";

            if (dt.Columns.Contains("Amount"))
                dt.Columns["Amount"].Caption = "ML";

            if (dt.Columns.Contains("Detail_NewPositionFactor"))
                dt.Columns["Detail_NewPositionFactor"].Caption = "PC1";

            if (dt.Columns.Contains("Detail_NewOverFactor"))
                dt.Columns["Detail_NewOverFactor"].Caption = "PC2";

            if (dt.Columns.Contains("Detail_NewJobFactor"))
                dt.Columns["Detail_NewJobFactor"].Caption = "PC3";

            if (dt.Columns.Contains("Detail_NewAreaFactor"))
                dt.Columns["Detail_NewAreaFactor"].Caption = "PC4";

            if (dt.Columns.Contains("Detail_InsTypeCode"))
                dt.Columns["Detail_InsTypeCode"].Caption = "PA";

            if (dt.Columns.Contains("MACV"))
                dt.Columns["MACV"].Caption = "MACV";

            if (dt.Columns.Contains("Employee_DateOfBirth"))
                dt.Columns["Employee_DateOfBirth"].Caption = "NGAYSINH";

            if (dt.Columns.Contains("Employee_Sex"))
                dt.Columns["Employee_Sex"].Caption = "GIOITINH";

            if (dt.Columns.Contains("MADT"))
                dt.Columns["MADT"].Caption = "MADT";

            if (dt.Columns.Contains("MAPB"))
                dt.Columns["MAPB"].Caption = "MAPB";

            if (dt.Columns.Contains("Detail_RegionCode"))
                dt.Columns["Detail_RegionCode"].Caption = "MA_TINH";

            if (dt.Columns.Contains("Detail_HospitalCode"))
                dt.Columns["Detail_HospitalCode"].Caption = "MA_BV";

            if (dt.Columns.Contains("Employee_MedicalNo"))
                dt.Columns["Employee_MedicalNo"].Caption = "SOKCB";

            if (dt.Columns.Contains("Employee_IDCardNo"))
                dt.Columns["Employee_IDCardNo"].Caption = "SOCMND";

            if (dt.Columns.Contains("Employee_IDCardIssueDate"))
                dt.Columns["Employee_IDCardIssueDate"].Caption = "NGAYCMND";

            if (dt.Columns.Contains("Employee_IDCardIssueAuthority"))
                dt.Columns["Employee_IDCardIssueAuthority"].Caption = "NOICAP";

            if (dt.Columns.Contains("MA_TINHCMT"))
                dt.Columns["MA_TINHCMT"].Caption = "MA_TINHCMT";

            if (dt.Columns.Contains("NOIKHAI"))
                dt.Columns["NOIKHAI"].Caption = "NOIKHAI";

            if (dt.Columns.Contains("Employee_NativeCountry"))
                dt.Columns["Employee_NativeCountry"].Caption = "NGUYENQUAN";

            if (dt.Columns.Contains("Employee_Address"))
                dt.Columns["Employee_Address"].Caption = "DIACHI";

            if (dt.Columns.Contains("Detail_Description"))
                dt.Columns["Detail_Description"].Caption = "GHICHU";

            if (dt.Columns.Contains("Detail_TakeCard"))
                dt.Columns["Detail_TakeCard"].Caption = "TRATHE";

            ReadExcelFile.exportDataToExcel(dt, _FileName);
        }

        private void ExportData2Excel(string _Title, DataSet ds, string _FileName, bool _IsMau02)
        {
            foreach (DataTable dt in ds.Tables)
            {
                if (dt.Columns.Contains("RowNumber"))
                    dt.Columns["RowNumber"].Caption = "STT";

                if (dt.Columns.Contains("Detail_InsBookNo"))
                    dt.Columns["Detail_InsBookNo"].Caption = "SOBHXH";

                if (dt.Columns.Contains("FullName"))
                    dt.Columns["FullName"].Caption = "HOTEN";

                if (dt.Columns.Contains("Detail_FromDate"))
                    dt.Columns["Detail_FromDate"].Caption = "TUTHANG";

                if (dt.Columns.Contains("Detail_ToDate"))
                    dt.Columns["Detail_ToDate"].Caption = "DENTHANG";

                if (dt.Columns.Contains("InsRate"))
                    dt.Columns["InsRate"].Caption = "TYLE";

                if (dt.Columns.Contains("OldAmount"))
                    dt.Columns["OldAmount"].Caption = "ML_DK";

                if (dt.Columns.Contains("OldMedicalAmount"))
                    dt.Columns["OldMedicalAmount"].Caption = "MLYT_DK";

                if (dt.Columns.Contains("NewAmount"))
                    dt.Columns["NewAmount"].Caption = "ML";

                if (dt.Columns.Contains("NewMedicalAmount"))
                    dt.Columns["NewMedicalAmount"].Caption = "MLYT";

                if (dt.Columns.Contains("Detail_OldBasicFactor"))
                    dt.Columns["Detail_OldBasicFactor"].Caption = "HSL_DK";

                if (dt.Columns.Contains("Detail_OldPositionFactor"))
                    dt.Columns["Detail_OldPositionFactor"].Caption = "PC1_DK";

                if (dt.Columns.Contains("Detail_OldOverFactor"))
                    dt.Columns["Detail_OldOverFactor"].Caption = "PC2_DK";

                if (dt.Columns.Contains("Detail_OldJobFactor"))
                    dt.Columns["Detail_OldJobFactor"].Caption = "PC3_DK";

                if (dt.Columns.Contains("Detail_OldAreaFactor"))
                    dt.Columns["Detail_OldAreaFactor"].Caption = "PC4_DK";

                if (dt.Columns.Contains("Detail_NewBasicFactor"))
                    dt.Columns["Detail_NewBasicFactor"].Caption = "HSL";

                if (dt.Columns.Contains("Amount"))
                    dt.Columns["Amount"].Caption = "ML";

                if (dt.Columns.Contains("Detail_NewPositionFactor"))
                    dt.Columns["Detail_NewPositionFactor"].Caption = "PC1";

                if (dt.Columns.Contains("Detail_NewOverFactor"))
                    dt.Columns["Detail_NewOverFactor"].Caption = "PC2";

                if (dt.Columns.Contains("Detail_NewJobFactor"))
                    dt.Columns["Detail_NewJobFactor"].Caption = "PC3";

                if (dt.Columns.Contains("Detail_NewAreaFactor"))
                    dt.Columns["Detail_NewAreaFactor"].Caption = "PC4";

                if (dt.Columns.Contains("Detail_InsTypeCode"))
                    dt.Columns["Detail_InsTypeCode"].Caption = "PA";

                if (dt.Columns.Contains("MACV"))
                    dt.Columns["MACV"].Caption = "MACV";

                if (dt.Columns.Contains("Employee_DateOfBirth"))
                    dt.Columns["Employee_DateOfBirth"].Caption = "NGAYSINH";

                if (dt.Columns.Contains("Employee_Sex"))
                    dt.Columns["Employee_Sex"].Caption = "GIOITINH";

                if (dt.Columns.Contains("MADT"))
                    dt.Columns["MADT"].Caption = "MADT";

                if (dt.Columns.Contains("MAPB"))
                    dt.Columns["MAPB"].Caption = "MAPB";

                if (dt.Columns.Contains("Detail_RegionCode"))
                    dt.Columns["Detail_RegionCode"].Caption = "MA_TINH";

                if (dt.Columns.Contains("Detail_HospitalCode"))
                    dt.Columns["Detail_HospitalCode"].Caption = "MA_BV";

                if (dt.Columns.Contains("Employee_MedicalNo"))
                    dt.Columns["Employee_MedicalNo"].Caption = "SOKCB";

                if (dt.Columns.Contains("Employee_IDCardNo"))
                    dt.Columns["Employee_IDCardNo"].Caption = "SOCMND";

                if (dt.Columns.Contains("Employee_IDCardIssueDate"))
                    dt.Columns["Employee_IDCardIssueDate"].Caption = "NGAYCMND";

                if (dt.Columns.Contains("Employee_IDCardIssueAuthority"))
                    dt.Columns["Employee_IDCardIssueAuthority"].Caption = "NOICAP";

                if (dt.Columns.Contains("MA_TINHCMT"))
                    dt.Columns["MA_TINHCMT"].Caption = "MA_TINHCMT";

                if (dt.Columns.Contains("NOIKHAI"))
                    dt.Columns["NOIKHAI"].Caption = "NOIKHAI";

                if (dt.Columns.Contains("Employee_NativeCountry"))
                    dt.Columns["Employee_NativeCountry"].Caption = "NGUYENQUAN";

                if (dt.Columns.Contains("Employee_Address"))
                    dt.Columns["Employee_Address"].Caption = "DIACHI";

                if (dt.Columns.Contains("Detail_Description"))
                    dt.Columns["Detail_Description"].Caption = "GHICHU";

                if (dt.Columns.Contains("Detail_TakeCard"))
                    dt.Columns["Detail_TakeCard"].Caption = "TRATHE";
            }
            ReadExcelFile.exportDataToExcel(ds, _FileName);
        }
    }
}
